package simorg.domain.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import simorg.domain.Assignment;
import simorg.domain.Compound;
import simorg.util.DatabaseConnection;

public class CompoundAssignmentJoinTable {

	public static void bootstrap(boolean drop) throws Exception {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			Statement stmt = null;
		    String query = null;
		    if ( drop ) {
		       query = "DROP TABLE IF EXISTS COMPOUND_ASSIGNMENT";
		    	stmt = dc.getConnection().createStatement();
			    stmt.execute(query);
		    }
		    query = "CREATE TABLE IF NOT EXISTS COMPOUND_ASSIGNMENT" +
	    	" (COMPOUND_ID INT, ASSIGNMENT_ID INT, PRIMARY KEY (COMPOUND_ID, ASSIGNMENT_ID), " +
	    	" FOREIGN KEY (COMPOUND_ID) REFERENCES COMPOUND(ID), " +
	    	" FOREIGN KEY (ASSIGNMENT_ID) REFERENCES ASSIGNMENT(ID))";
		    stmt = dc.getConnection().createStatement();
		    stmt.execute(query);
		   
		    
		    for ( Assignment a : AssignmentTable.findAll(dc)) {
		    	ArrayList<Integer> compounds = new ArrayList<Integer>();
		    	for ( Compound c : CompoundTable.findAll(dc)) {
		    		compounds.add(c.getId());
		    	}
		    	setCompoundsForAssignment(dc, a.getId(), compounds);
		    	
		    	System.out.println("Bootstrapped Compounds for assignment " + a.getId());
		    	for ( Compound c : findCompoundsByAssignment(dc, a.getId())) {
		    		System.out.println(" " + c.getId() + " -> " + c.getLabel());
		    	}
		    }
		    
		    
		    
		}
		finally {
			dc.getConnection().close();
		}
	}
		
	public static Collection<Compound> findCompoundsByAssignment(DatabaseConnection dc, int assignmentId) {
		ArrayList<Compound> retval = new ArrayList<Compound>();
		try {
			String query = "SELECT COMPOUND.ID, COMPOUND.LABEL, COMPOUND.CLASS, COMPOUND.BOILING_POINT, COMPOUND.MELTING_POINT FROM COMPOUND_ASSIGNMENT JOIN COMPOUND ON COMPOUND_ID=COMPOUND.ID " + 
				"WHERE ASSIGNMENT_ID=?";
			PreparedStatement stmt = dc.getConnection().prepareStatement(query);
			stmt.setInt(1, assignmentId);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()){
				Compound compound = new Compound();
				CompoundTable.fillFromResultSet(compound, rs);
		        retval.add(compound);
		    }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return retval;
	}
	public static void setCompoundsForAssignment(DatabaseConnection dc, int assignmentId, Collection<Integer> compoundIds){
		try {
			// first removes all the compounds currently associated with the assignment, 
			String query = "DELETE COMPOUND_ASSIGNMENT WHERE ASSIGNMENT_ID=?";
			PreparedStatement remove = dc.getConnection().prepareStatement(query);
			remove.setInt(1, assignmentId);
			remove.executeUpdate();
			// then inserts new associations for all the given compounds.
			if ( compoundIds == null ) return;
			for ( int cid : compoundIds ) {
				query = "INSERT INTO COMPOUND_ASSIGNMENT (COMPOUND_ID, ASSIGNMENT_ID ) " +
				"VALUES ( ?, ?)";
				PreparedStatement insert = dc.getConnection().prepareStatement(query);
				insert.setInt(1, cid);
				insert.setInt(2, assignmentId);
				insert.executeUpdate();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
